from fastapi import FastAPI, Depends, HTTPException, status
from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm
from jose import JWTError, jwt
from passlib.context import CryptContext
from datetime import datetime, timedelta
from typing import Optional, List
import databases
import sqlalchemy
from pydantic import BaseModel, EmailStr, Field
from enum import Enum
import os
from dotenv import load_dotenv
from passlib.context import CryptContext
from sqlalchemy.sql import func  # 添加导入
import pymysql

pymysql.install_as_MySQLdb()

# 加载环境变量
load_dotenv()

# 数据库配置
DATABASE_URL = os.getenv(
    "DATABASE_URL", "mysql+pymysql://user:password@localhost/teahouse"
)
database = databases.Database(DATABASE_URL)

# 创建SQLAlchemy引擎和元数据
metadata = sqlalchemy.MetaData()

# 定义用户表
users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("username", sqlalchemy.String(50), unique=True),
    sqlalchemy.Column("email", sqlalchemy.String(100), unique=True),
    sqlalchemy.Column("password", sqlalchemy.String(255)),
    sqlalchemy.Column("phone", sqlalchemy.String(20)),
    sqlalchemy.Column("role", sqlalchemy.String(20), default="user"),
    sqlalchemy.Column(
        "create_time", sqlalchemy.DateTime, server_default=func.now(), nullable=False
    ),
    sqlalchemy.Column("disabled", sqlalchemy.Boolean, default=False),  # 确保存在该列
)

# 定义茶室表
teahouses = sqlalchemy.Table(
    "teahouses",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String(100)),
    sqlalchemy.Column("address", sqlalchemy.String(200)),
    sqlalchemy.Column("city", sqlalchemy.String(50)),
    sqlalchemy.Column("phone", sqlalchemy.String(20)),
    sqlalchemy.Column("opening_hours", sqlalchemy.String(50)),
    sqlalchemy.Column("description", sqlalchemy.Text),
    sqlalchemy.Column("status", sqlalchemy.String(20), default="open"),
    sqlalchemy.Column("longitude", sqlalchemy.Float),
    sqlalchemy.Column("latitude", sqlalchemy.Float),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义房间表
rooms = sqlalchemy.Table(
    "rooms",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column(
        "teahouse_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("teahouses.id")
    ),
    sqlalchemy.Column("name", sqlalchemy.String(100)),
    sqlalchemy.Column("room_type", sqlalchemy.String(20)),
    sqlalchemy.Column("capacity", sqlalchemy.Integer),
    sqlalchemy.Column("price_per_hour", sqlalchemy.Float),
    sqlalchemy.Column("description", sqlalchemy.Text),
    sqlalchemy.Column("status", sqlalchemy.String(20), default="available"),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义订单表
orders = sqlalchemy.Table(
    "orders",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column(
        "teahouse_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("teahouses.id")
    ),
    sqlalchemy.Column("room_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("rooms.id")),
    sqlalchemy.Column("user_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("users.id")),
    sqlalchemy.Column("start_time", sqlalchemy.DateTime),
    sqlalchemy.Column("end_time", sqlalchemy.DateTime),
    sqlalchemy.Column("status", sqlalchemy.String(20), default="pending"),
    sqlalchemy.Column("total_amount", sqlalchemy.Float),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义广告表
advertisements = sqlalchemy.Table(
    "advertisements",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("title", sqlalchemy.String(100)),
    sqlalchemy.Column("content", sqlalchemy.Text),
    sqlalchemy.Column("image_url", sqlalchemy.String(255)),
    sqlalchemy.Column("start_date", sqlalchemy.Date),
    sqlalchemy.Column("end_date", sqlalchemy.Date),
    sqlalchemy.Column(
        "teahouse_id",
        sqlalchemy.Integer,
        sqlalchemy.ForeignKey("teahouses.id"),
        nullable=True,
    ),
    sqlalchemy.Column("status", sqlalchemy.String(20), default="inactive"),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义活动表
activities = sqlalchemy.Table(
    "activities",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column(
        "teahouse_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("teahouses.id")
    ),
    sqlalchemy.Column("title", sqlalchemy.String(100)),
    sqlalchemy.Column("description", sqlalchemy.Text),
    sqlalchemy.Column("start_time", sqlalchemy.DateTime),
    sqlalchemy.Column("end_time", sqlalchemy.DateTime),
    sqlalchemy.Column("price", sqlalchemy.Float),
    sqlalchemy.Column("max_participants", sqlalchemy.Integer),
    sqlalchemy.Column("status", sqlalchemy.String(20), default="pending"),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义二级账户表
subaccounts = sqlalchemy.Table(
    "subaccounts",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("user_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("users.id")),
    sqlalchemy.Column("username", sqlalchemy.String(50)),
    sqlalchemy.Column("role", sqlalchemy.String(20)),
    sqlalchemy.Column("permissions", sqlalchemy.Text),
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 定义积分表
points = sqlalchemy.Table(
    "points",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("user_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("users.id")),
    sqlalchemy.Column("current_points", sqlalchemy.Integer, default=0),
    sqlalchemy.Column("total_earned", sqlalchemy.Integer, default=0),
    sqlalchemy.Column("total_redeemed", sqlalchemy.Integer, default=0),
    sqlalchemy.Column("last_updated", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 设备表
devices = sqlalchemy.Table(
    "devices",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("user_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("users.id")),
    sqlalchemy.Column("name", sqlalchemy.String(100)),  # 设备名称
    sqlalchemy.Column(
        "device_type", sqlalchemy.String(50)
    ),  # 设备类型（如 thermostat, light）
    sqlalchemy.Column("serial_number", sqlalchemy.String(50)),  # 设备序列号
    sqlalchemy.Column("firmware_version", sqlalchemy.String(20)),  # 固件版本
    sqlalchemy.Column("status", sqlalchemy.String(20)),  # 状态（online/offline）
    sqlalchemy.Column("last_active", sqlalchemy.DateTime),  # 最后活跃时间
    sqlalchemy.Column("metadata", sqlalchemy.JSON),  # 元数据（温度、亮度等）
    sqlalchemy.Column("create_time", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 通讯记录表（设备与服务器的通讯日志）
device_communications = sqlalchemy.Table(
    "device_communications",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column(
        "device_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("devices.id")
    ),
    sqlalchemy.Column(
        "communication_type", sqlalchemy.String(20)
    ),  # 通讯类型（command/response/data）
    sqlalchemy.Column("message", sqlalchemy.Text),  # 通讯内容
    sqlalchemy.Column("timestamp", sqlalchemy.DateTime, default=datetime.utcnow),
)

# 安全配置
SECRET_KEY = os.getenv("SECRET_KEY", "your-secret-key")
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 30

# 创建FastAPI应用
app = FastAPI(title="共享茶室API服务", description="提供茶室、房间、订单等管理功能")

# 密码哈希
pwd_context = CryptContext(schemes=["sha256_crypt"], deprecated="auto")

# OAuth2认证
oauth2_scheme = OAuth2PasswordBearer(tokenUrl="token")


# 模型定义
class UserBase(BaseModel):
    username: str
    email: EmailStr


class UserCreate(BaseModel):
    username: str
    email: EmailStr
    password: str
    phone: Optional[str] = None
    # 添加创建时间字段
    # create_time: datetime = datetime.utcnow


class User(UserBase):
    id: int
    role: str
    disabled: bool  # 添加 disabled 字段
    create_time: datetime

    class Config:
        orm_mode = True


class Token(BaseModel):
    access_token: str
    token_type: str


class TokenData(BaseModel):
    username: Optional[str] = None


class TeahouseBase(BaseModel):
    name: str
    address: str
    city: str
    phone: str
    opening_hours: str
    description: Optional[str] = None
    status: Optional[str] = "open"
    longitude: float
    latitude: float


class TeahouseCreate(TeahouseBase):
    pass


class Teahouse(TeahouseBase):
    id: int
    create_time: datetime
    room_count: Optional[int] = 0
    average_rating: Optional[float] = 0.0
    location: Optional[dict] = None
    map_url: Optional[str] = None
    photos: Optional[List[str]] = []

    class Config:
        orm_mode = True


class RoomBase(BaseModel):
    name: str
    room_type: str
    capacity: int
    price_per_hour: float
    description: Optional[str] = None
    status: Optional[str] = "available"


class RoomCreate(RoomBase):
    pass


class Room(RoomBase):
    id: int
    teahouse_id: int
    amenities: Optional[List[str]] = []
    photos: Optional[List[str]] = []
    create_time: datetime

    class Config:
        orm_mode = True


class OrderBase(BaseModel):
    teahouse_id: int
    room_id: int
    user_id: int
    start_time: datetime
    end_time: datetime
    guest_count: int
    remarks: Optional[str] = None


class OrderCreate(OrderBase):
    pass


class Order(OrderBase):
    id: int
    status: str
    total_amount: float
    create_time: datetime

    class Config:
        orm_mode = True


class AdvertisementBase(BaseModel):
    title: str
    content: str
    image_url: Optional[str] = None
    start_date: str
    end_date: str
    teahouse_id: Optional[int] = None


class AdvertisementCreate(AdvertisementBase):
    pass


class Advertisement(AdvertisementBase):
    id: int
    status: str
    create_time: datetime

    class Config:
        orm_mode = True


class ActivityBase(BaseModel):
    title: str
    description: str
    start_time: datetime
    end_time: datetime
    price: float
    max_participants: int


class ActivityCreate(ActivityBase):
    pass


class Activity(ActivityBase):
    id: int
    teahouse_id: int
    status: str
    create_time: datetime

    class Config:
        orm_mode = True


class SubaccountBase(BaseModel):
    username: str
    role: str
    permissions: List[str]


class SubaccountCreate(SubaccountBase):
    pass


class Subaccount(SubaccountBase):
    id: int
    user_id: int
    create_time: datetime

    class Config:
        orm_mode = True


class Points(BaseModel):
    user_id: int
    current_points: int
    total_earned: int
    total_redeemed: int
    last_updated: datetime

    class Config:
        orm_mode = True


class DeviceBase(BaseModel):
    name: str
    device_type: str
    serial_number: str
    firmware_version: Optional[str] = "1.0.0"
    metadata: Optional[dict] = {}


class DeviceCreate(DeviceBase):
    pass


class Device(DeviceBase):
    id: int
    user_id: int
    status: str
    last_active: Optional[datetime]
    create_time: datetime

    class Config:
        orm_mode = True


class DeviceCommunicationBase(BaseModel):
    communication_type: str
    message: str


class DeviceCommunicationCreate(DeviceCommunicationBase):
    pass


class DeviceCommunication(DeviceCommunicationBase):
    id: int
    device_id: int
    timestamp: datetime

    class Config:
        orm_mode = True


# 数据库操作
async def get_user(username: str):
    query = users.select().where(users.c.username == username)
    return await database.fetch_one(query)


async def get_user_by_id(user_id: int):
    query = users.select().where(users.c.id == user_id)
    return await database.fetch_one(query)


async def get_user_by_email(email: str):
    query = users.select().where(users.c.email == email)
    return await database.fetch_one(query)


async def authenticate_user(username: str, password: str):
    user = await get_user(username)
    if not user:
        return False
    if not pwd_context.verify(password, user.password):
        return False
    return user


async def create_access_token(data: dict, expires_delta: Optional[timedelta] = None):
    to_encode = data.copy()
    if expires_delta:
        expire = datetime.utcnow() + expires_delta
    else:
        expire = datetime.utcnow() + timedelta(minutes=15)
    to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt


async def get_current_user(token: str = Depends(oauth2_scheme)):
    credentials_exception = HTTPException(
        status_code=status.HTTP_401_UNAUTHORIZED,
        detail="Could not validate credentials",
        headers={"WWW-Authenticate": "Bearer"},
    )
    try:
        payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
        username: str = payload.get("sub")
        if username is None:
            raise credentials_exception
        token_data = TokenData(username=username)
    except JWTError:
        raise credentials_exception
    user = await get_user(username=token_data.username)
    if user is None:
        raise credentials_exception
    return user


async def get_current_active_user(current_user: User = Depends(get_current_user)):
    if current_user.disabled:
        raise HTTPException(status_code=400, detail="Inactive user")
    return current_user


# 事件处理
@app.on_event("startup")
async def startup():
    await database.connect()


@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()


# 用户管理API
@app.post("/token", response_model=Token)
async def login_for_access_token(form_data: OAuth2PasswordRequestForm = Depends()):
    user = await authenticate_user(form_data.username, form_data.password)
    if not user:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Incorrect username or password",
            headers={"WWW-Authenticate": "Bearer"},
        )
    access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
    access_token = await create_access_token(
        data={"sub": user.username}, expires_delta=access_token_expires
    )
    return {"access_token": access_token, "token_type": "bearer"}


@app.get("/users/", response_model=List[User])
async def read_users(
    skip: int = 0,
    limit: int = 100,
    current_user: User = Depends(get_current_active_user),
):
    query = users.select().offset(skip).limit(limit)
    return await database.fetch_all(query)


@app.post("/users/", response_model=User)
async def create_user(user: UserCreate):
    """创建新用户"""
    # 检查用户名是否已存在
    existing_user = await get_user(user.username)
    if existing_user:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Username already exists"
        )
    existing_email = await get_user_by_email(user.email)  # 添加查询函数
    if existing_email:
        raise HTTPException(status_code=400, detail="Email address already registered")
    # 对密码进行哈希处理
    hashed_password = pwd_context.hash(user.password)
    now = datetime.now()
    # 插入新用户
    query = users.insert().values(
        username=user.username,
        email=user.email,
        password=hashed_password,
        phone=user.phone,
        role="user",  # 默认角色为普通用户
        create_time=now.replace(microsecond=0),
        disabled=False,
    )

    # 获取插入后的用户ID
    last_record_id = await database.execute(query)

    # 返回创建的用户信息（不包含密码）
    return {
        **user.dict(),
        "id": last_record_id,
        "role": "user",
        "disabled": False,
        "create_time": datetime.utcnow(),
    }


@app.get("/users/{user_id}", response_model=User)
async def read_user(
    user_id: int, current_user: User = Depends(get_current_active_user)
):
    if user_id == 0:
        query = users.select().where(users.c.id == current_user.id)
    else:
        query = users.select().where(users.c.id == user_id)
    user = await database.fetch_one(query)
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return user


@app.put("/users/{user_id}", response_model=User)
async def update_user(
    user_id: int,
    updated_user: UserCreate,  # 假设 UserCreate 包含 email 字段
    current_user: User = Depends(get_current_active_user),
):
    # 权限校验（确保用户只能更新自己或管理员可更新所有用户）
    if current_user.id != user_id or current_user.role != "admin":
        raise HTTPException(status_code=403, detail="权限不足")

    # 邮箱唯一性检查（排除当前用户自身）
    if updated_user.email:
        existing_user = await get_user_by_email(updated_user.email)
        if existing_user and existing_user.id != user_id:
            raise HTTPException(
                status_code=400, detail=f"邮箱 '{updated_user.email}' 已被其他用户使用"
            )

    # 构建更新数据（仅包含有值的字段）
    update_data = {}
    if updated_user.username:
        update_data["username"] = updated_user.username
    if updated_user.email:
        update_data["email"] = updated_user.email
    if updated_user.password:
        update_data["password"] = pwd_context.hash(updated_user.password)
    if updated_user.phone:
        update_data["phone"] = updated_user.phone

    # 执行更新
    query = users.update().where(users.c.id == user_id).values(**update_data)
    await database.execute(query)

    # 返回更新后的用户数据
    updated_user_data = await database.fetch_one(
        users.select().where(users.c.id == user_id)
    )
    return updated_user_data


@app.delete("/users/{user_id}")
async def delete_user(
    user_id: int, current_user: User = Depends(get_current_active_user)
):
    query = users.delete().where(users.c.id == user_id)
    await database.execute(query)
    return {"message": "User deleted successfully"}


# 茶室管理API
@app.get("/teahouses/", response_model=List[Teahouse])
async def read_teahouses(
    city: Optional[str] = None,
    name: Optional[str] = None,
    longitude: Optional[float] = None,
    latitude: Optional[float] = None,
    radius: Optional[float] = None,
    skip: int = 0,
    limit: int = 100,
    current_user: User = Depends(get_current_active_user),
):
    query = teahouses.select()

    if city:
        query = query.where(teahouses.c.city == city)

    if name:
        query = query.where(teahouses.c.name.ilike(f"%{name}%"))

    # 简化的距离计算（实际应用中应使用更精确的地理计算）
    if longitude and latitude and radius:
        query = query.where(
            sqlalchemy.func.sqrt(
                sqlalchemy.func.pow(teahouses.c.longitude - longitude, 2)
                + sqlalchemy.func.pow(teahouses.c.latitude - latitude, 2)
            )
            <= radius
        )

    query = query.offset(skip).limit(limit)
    results = await database.fetch_all(query)

    # 添加距离信息
    teahouses_list = []
    for result in results:
        teahouse = dict(result)
        if longitude and latitude:
            distance = round(
                (
                    (teahouse["longitude"] - longitude) ** 2
                    + (teahouse["latitude"] - latitude) ** 2
                )
                ** 0.5,
                2,
            )
            teahouse["distance"] = distance
        teahouse["location"] = {
            "longitude": teahouse["longitude"],
            "latitude": teahouse["latitude"],
        }
        teahouse["map_url"] = (
            f"https://maps.example.com/?q={teahouse['latitude']},{teahouse['longitude']}"
        )
        teahouses_list.append(teahouse)

    return teahouses_list


@app.post("/teahouses/", response_model=Teahouse)
async def create_teahouse(
    teahouse: TeahouseCreate, current_user: User = Depends(get_current_active_user)
):
    query = teahouses.insert().values(**teahouse.dict())
    last_record_id = await database.execute(query)
    return {**teahouse.dict(), "id": last_record_id}


@app.get("/teahouses/{teahouse_id}", response_model=Teahouse)
async def read_teahouse(
    teahouse_id: int, current_user: User = Depends(get_current_active_user)
):
    query = teahouses.select().where(teahouses.c.id == teahouse_id)
    teahouse = await database.fetch_one(query)
    if teahouse is None:
        raise HTTPException(status_code=404, detail="Teahouse not found")

    # 获取房间数量
    room_count_query = rooms.select().where(rooms.c.teahouse_id == teahouse_id)
    rooms_result = await database.fetch_all(room_count_query)

    result = dict(teahouse)
    result["room_count"] = len(rooms_result)
    result["average_rating"] = 4.8  # 示例值
    result["location"] = {
        "longitude": result["longitude"],
        "latitude": result["latitude"],
    }
    result["map_url"] = (
        f"https://maps.example.com/?q={result['latitude']},{result['longitude']}"
    )
    result["photos"] = [
        f"https://example.com/teahouse_photos/th-{teahouse_id}-{i}.jpg"
        for i in range(1, 3)
    ]

    return result


@app.put("/teahouses/{teahouse_id}", response_model=Teahouse)
async def update_teahouse(
    teahouse_id: int,
    teahouse: TeahouseCreate,
    current_user: User = Depends(get_current_active_user),
):
    query = (
        teahouses.update()
        .where(teahouses.c.id == teahouse_id)
        .values(**teahouse.dict())
    )
    await database.execute(query)
    return {**teahouse.dict(), "id": teahouse_id}


# 房间管理API
@app.get("/teahouses/{teahouse_id}/rooms/", response_model=List[Room])
async def read_rooms(
    teahouse_id: int,
    status: Optional[str] = None,
    room_type: Optional[str] = None,
    current_user: User = Depends(get_current_active_user),
):
    query = rooms.select().where(rooms.c.teahouse_id == teahouse_id)

    if status:
        query = query.where(rooms.c.status == status)

    if room_type:
        query = query.where(rooms.c.room_type == room_type)

    results = await database.fetch_all(query)

    # 添加房间设施和照片信息
    rooms_list = []
    for result in results:
        room = dict(result)
        room["amenities"] = ["Wi-Fi", "空调", "茶具"]
        room["photos"] = [f"https://example.com/room_photos/rm-{room['id']}-1.jpg"]
        rooms_list.append(room)

    return rooms_list


@app.post("/teahouses/{teahouse_id}/rooms/", response_model=Room)
async def create_room(
    room: RoomCreate,
    teahouse_id: int,
    current_user: User = Depends(get_current_active_user),
):
    query = rooms.insert().values(teahouse_id=teahouse_id, **room.dict())
    last_record_id = await database.execute(query)
    return {**room.dict(), "id": last_record_id, "teahouse_id": teahouse_id}


@app.get("/teahouses/{teahouse_id}/rooms/{room_id}", response_model=Room)
async def read_room(
    teahouse_id: int,
    room_id: int,
    current_user: User = Depends(get_current_active_user),
):
    query = rooms.select().where(
        rooms.c.id == room_id, rooms.c.teahouse_id == teahouse_id
    )
    room = await database.fetch_one(query)
    if room is None:
        raise HTTPException(status_code=404, detail="Room not found")

    result = dict(room)
    result["amenities"] = ["Wi-Fi", "空调", "茶具"]
    result["photos"] = [
        f"https://example.com/room_photos/rm-{room_id}-{i}.jpg" for i in range(1, 3)
    ]

    return result


@app.put("/teahouses/{teahouse_id}/rooms/{room_id}", response_model=Room)
async def update_room(
    teahouse_id: int,
    room_id: int,
    room: RoomCreate,
    current_user: User = Depends(get_current_active_user),
):
    query = (
        rooms.update()
        .where(rooms.c.id == room_id, rooms.c.teahouse_id == teahouse_id)
        .values(**room.dict())
    )
    await database.execute(query)
    return {**room.dict(), "id": room_id, "teahouse_id": teahouse_id}


# 订单管理API
@app.post("/orders/", response_model=Order)
async def create_order(
    order: OrderCreate, current_user: User = Depends(get_current_active_user)
):
    # 计算总金额（示例：假设每小时价格为288元）
    room_query = rooms.select().where(rooms.c.id == order.room_id)
    room = await database.fetch_one(room_query)

    if not room:
        raise HTTPException(status_code=404, detail="Room not found")

    duration = (order.end_time - order.start_time).total_seconds() / 3600
    total_amount = duration * room.price_per_hour

    query = orders.insert().values(
        teahouse_id=order.teahouse_id,
        room_id=order.room_id,
        user_id=order.user_id,
        start_time=order.start_time,
        end_time=order.end_time,
        status="pending",
        total_amount=total_amount,
    )

    last_record_id = await database.execute(query)
    return {
        **order.dict(),
        "id": last_record_id,
        "status": "pending",
        "total_amount": total_amount,
    }


@app.get("/users/{user_id}/orders/", response_model=List[Order])
async def read_user_orders(
    user_id: int,
    status: Optional[str] = None,
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    current_user: User = Depends(get_current_active_user),
):
    query = orders.select().where(orders.c.user_id == user_id)

    if status:
        query = query.where(orders.c.status == status)

    if start_date:
        query = query.where(orders.c.start_time >= datetime.fromisoformat(start_date))

    if end_date:
        query = query.where(orders.c.end_time <= datetime.fromisoformat(end_date))

    return await database.fetch_all(query)


# 广告管理API
@app.post("/advertisements/", response_model=Advertisement)
async def create_advertisement(
    ad: AdvertisementCreate, current_user: User = Depends(get_current_active_user)
):
    # 确定广告状态
    today = datetime.now().date()
    status = (
        "active" if (today >= ad.start_date and today <= ad.end_date) else "inactive"
    )

    query = advertisements.insert().values(
        title=ad.title,
        content=ad.content,
        image_url=ad.image_url,
        start_date=ad.start_date,
        end_date=ad.end_date,
        teahouse_id=ad.teahouse_id,
        status=status,
    )

    last_record_id = await database.execute(query)
    return {**ad.dict(), "id": last_record_id, "status": status}


# 活动管理API
@app.post("/teahouses/{teahouse_id}/activities/", response_model=Activity)
async def create_activity(
    activity: ActivityCreate,
    teahouse_id: int,
    current_user: User = Depends(get_current_active_user),
):
    # 确定活动状态
    now = datetime.now()
    status = (
        "ongoing"
        if (activity.start_time <= now and activity.end_time >= now)
        else "pending"
    )

    query = activities.insert().values(
        teahouse_id=teahouse_id,
        title=activity.title,
        description=activity.description,
        start_time=activity.start_time,
        end_time=activity.end_time,
        price=activity.price,
        max_participants=activity.max_participants,
        status=status,
    )

    last_record_id = await database.execute(query)
    return {
        **activity.dict(),
        "id": last_record_id,
        "teahouse_id": teahouse_id,
        "status": status,
    }


# 二级账户管理API
@app.post("/users/{user_id}/subaccounts/", response_model=Subaccount)
async def create_subaccount(
    subaccount: SubaccountCreate,
    user_id: int,
    current_user: User = Depends(get_current_active_user),
):
    query = subaccounts.insert().values(
        user_id=user_id,
        username=subaccount.username,
        role=subaccount.role,
        permissions=",".join(subaccount.permissions),
    )

    last_record_id = await database.execute(query)
    return {**subaccount.dict(), "id": last_record_id, "user_id": user_id}


# 积分管理API
@app.get("/users/{user_id}/points/", response_model=Points)
async def read_user_points(
    user_id: int, current_user: User = Depends(get_current_active_user)
):
    query = points.select().where(points.c.user_id == user_id)
    user_points = await database.fetch_one(query)

    if not user_points:
        # 如果用户积分记录不存在，创建一条新记录
        query = points.insert().values(
            user_id=user_id,
            current_points=0,
            total_earned=0,
            total_redeemed=0,
            last_updated=datetime.now(),
        )
        await database.execute(query)
        user_points = await database.fetch_one(
            points.select().where(points.c.user_id == user_id)
        )

    return user_points


@app.post("/users/{user_id}/points/redeem/")
async def redeem_points(
    user_id: int,
    item_id: str,
    points: int,
    current_user: User = Depends(get_current_active_user),
):
    # 检查用户积分是否足够
    points_query = points.select().where(points.c.user_id == user_id)
    user_points = await database.fetch_one(points_query)

    if not user_points:
        raise HTTPException(status_code=404, detail="User points not found")

    if user_points.current_points < points:
        raise HTTPException(status_code=400, detail="Insufficient points")

    # 更新积分
    new_balance = user_points.current_points - points
    total_redeemed = user_points.total_redeemed + points

    update_query = (
        points.update()
        .where(points.c.user_id == user_id)
        .values(
            current_points=new_balance,
            total_redeemed=total_redeemed,
            last_updated=datetime.now(),
        )
    )

    await database.execute(update_query)

    return {
        "user_id": user_id,
        "item_id": item_id,
        "points_used": points,
        "new_balance": new_balance,
        "redeem_time": datetime.now().isoformat(),
    }


# 茶室介绍API
@app.get("/teahouses/{teahouse_id}/introduction/")
async def get_teahouse_introduction(
    teahouse_id: int, current_user: User = Depends(get_current_active_user)
):
    query = teahouses.select().where(teahouses.c.id == teahouse_id)
    teahouse = await database.fetch_one(query)

    if not teahouse:
        raise HTTPException(status_code=404, detail="Teahouse not found")

    return {
        "teahouse_id": teahouse.id,
        "name": teahouse.name,
        "description": f"{teahouse.name}创立于2010年，位于{teahouse.city}，是一家集传统茶艺展示、茶品销售于一体的高端茶室...",
        "history": f"茶室由国家级茶艺师XXX创办，秉持'以茶会友'的理念...",
        "teas": ["西湖龙井", "武夷岩茶", "普洱茶", "铁观音"],
        "amenities": ["免费停车", "Wi-Fi", "茶艺表演", "茶点供应"],
        "opening_hours": teahouse.opening_hours,
        "photos": [
            f"https://example.com/teahouse_photos/intro{i}.jpg" for i in range(1, 3)
        ],
        "video_url": "https://example.com/teahouse_video.mp4",
    }


# 设备管理 API
@app.get("/devices/", response_model=List[Device])
async def read_devices(
    user_id: Optional[int] = None,
    status: Optional[str] = None,
    skip: int = 0,
    limit: int = 100,
    current_user: User = Depends(get_current_active_user),
):
    """获取设备列表（支持用户筛选和状态筛选）"""
    query = devices.select()
    if user_id:
        query = query.where(devices.c.user_id == user_id)
    if status:
        query = query.where(devices.c.status == status)
    query = query.offset(skip).limit(limit)
    return await database.fetch_all(query)


@app.post("/devices/", response_model=Device)
async def create_device(
    device: DeviceCreate, current_user: User = Depends(get_current_active_user)
):
    """创建新设备"""
    query = devices.insert().values(
        user_id=current_user.id,
        name=device.name,
        device_type=device.device_type,
        serial_number=device.serial_number,
        firmware_version=device.firmware_version,
        metadata=device.metadata,
        status="pending",  # 初始状态为待激活
    )
    last_record_id = await database.execute(query)
    return {
        **device.dict(),
        "id": last_record_id,
        "user_id": current_user.id,
        "status": "pending",
    }


@app.get("/devices/{device_id}", response_model=Device)
async def read_device(
    device_id: int, current_user: User = Depends(get_current_active_user)
):
    """获取单个设备详情"""
    query = devices.select().where(
        devices.c.id == device_id, devices.c.user_id == current_user.id
    )
    device = await database.fetch_one(query)
    if not device:
        raise HTTPException(status_code=404, detail="Device not found")
    return device


@app.put("/devices/{device_id}", response_model=Device)
async def update_device(
    device_id: int,
    updated_device: DeviceCreate,
    current_user: User = Depends(get_current_active_user),
):
    """更新设备信息"""
    query = (
        devices.update()
        .where(devices.c.id == device_id, devices.c.user_id == current_user.id)
        .values(**updated_device.dict())
    )
    await database.execute(query)
    return {**updated_device.dict(), "id": device_id, "user_id": current_user.id}


@app.delete("/devices/{device_id}")
async def delete_device(
    device_id: int, current_user: User = Depends(get_current_active_user)
):
    """删除设备"""
    query = devices.delete().where(
        devices.c.id == device_id, devices.c.user_id == current_user.id
    )
    await database.execute(query)
    return {"message": "Device deleted successfully"}


@app.post("/devices/{device_id}/status/")
async def update_device_status(
    device_id: int,
    status: str,
    metadata: dict,
    current_user: User = Depends(get_current_active_user),
):
    """设备主动上报状态（如在线/离线、传感器数据）"""
    query = (
        devices.update()
        .where(devices.c.id == device_id, devices.c.user_id == current_user.id)
        .values(status=status, metadata=metadata, last_active=datetime.utcnow())
    )
    await database.execute(query)
    # 记录通讯日志
    await create_communication(
        device_id=device_id,
        comm=DeviceCommunicationCreate(
            communication_type="status_update",
            message=f"Device status updated to {status}",
        ),
    )
    return {"message": "Status updated successfully"}
